
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'ATM_QCTT')
DROP DATABASE [ATM_QCTT]
CREATE DATABASE [ATM_QCTT]
GO
USE [ATM_QCTT]
--- create table - TB_PERMISSION ---
GO
CREATE TABLE [TB_PERMISSION]
(
	[Per_ID] INT PRIMARY KEY,
	[Per_Name] VARCHAR(100) NOT NULL
)
--- create table - TB_ADMINISTRATOR ---
GO
CREATE TABLE [TB_ADMINISTRATOR]
(
	[Admin_ID] INT IDENTITY PRIMARY KEY,
	[Per_ID] INT FOREIGN KEY REFERENCES [TB_PERMISSION]([Per_ID]),
	[Admin_Username] VARCHAR(16) NOT NULL,
	[Admin_Password] VARCHAR(40) NOT NULL,
	[Admin_FullName] VARCHAR(40),
	[Admin_DoB] DATETIME,
	[Admin_Gender] BIT,
	[Admin_Address] VARCHAR(300),
	[Admin_Phone] VARCHAR(15),
	[Admin_Email] VARCHAR(40),
	[Admin_Status] BIT DEFAULT('TRUE') NOT NULL,
	[Admin_Delete] BIT DEFAULT('FALSE') NOT NULL,
	[Admin_Delete_Reason] VARCHAR(MAX)
)
--- create table - TB_USER ---
GO
CREATE TABLE [TB_USER]
(
	[User_ID] INT IDENTITY PRIMARY KEY,
	[User_ID_Card_No] VARCHAR(9) UNIQUE,
	[User_FullName] VARCHAR(40),
	[User_DoB] DATETIME,
	[User_Gender] BIT,
	[User_Phone] VARCHAR(15),
	[User_Address] VARCHAR(300),
	[User_Delete] BIT DEFAULT('FALSE'),
	[User_Delete_Reason] VARCHAR(MAX)
)
--- create table - TB_ACCOUNT ---
GO
CREATE TABLE [TB_ACCOUNT]
(
	[Acc_ID] INT IDENTITY PRIMARY KEY,
	[User_ID] INT FOREIGN KEY REFERENCES [TB_USER]([User_ID]),
	[Acc_No] VARCHAR(16) UNIQUE,
	[Acc_PIN] VARCHAR(40) NOT NULL,
	[Acc_Balance] FLOAT NOT NULL,
	[Acc_Status] BIT DEFAULT('TRUE'),
	[Acc_Delete] BIT DEFAULT('FALSE'),
	[Acc_Delete_Reason] VARCHAR(MAX),
)
--- create table - TB_TRANSACTION ---
GO
CREATE TABLE [TB_TRANSACTION]
(
	[Tran_ID] INT IDENTITY PRIMARY KEY,
	[Tran_Type] BIT NOT NULL,
	[Acc_Pri_ID] INT FOREIGN KEY REFERENCES [TB_ACCOUNT]([Acc_ID]),
	[Acc_Sec_ID] INT FOREIGN KEY REFERENCES [TB_ACCOUNT]([Acc_ID]),
	[Admin_ID] INT FOREIGN KEY REFERENCES [TB_ADMINISTRATOR]([Admin_ID]),
	[Tran_Amount] FLOAT NOT NULL,
	[Tran_Balance] FLOAT NOT NULL,
	[Tran_Reason] VARCHAR(MAX),
	[Tran_Date] DATETIME DEFAULT(GETDATE())
)
--- create table - TB_BANK_INFO ---
GO
CREATE TABLE [TB_BANK_INFO]
(
	[Bank_ID] INT PRIMARY KEY,
	[Bank_Name] VARCHAR(40) NOT NULL,
	[Bank_Address] VARCHAR(300) NOT NULL,
	[Bank_Phone] VARCHAR(15),
	[Bank_Fax] VARCHAR(15),
)
--- create table - TB_ATM_INFO ---
GO
CREATE TABLE [TB_ATM_INFO]
(
	[Sys_ID] INT PRIMARY KEY,
	[Sys_WA] FLOAT NOT NULL, -- Maximum amount of withdrawal in a single day
	[Sys_DA] FLOAT NOT NULL, -- Maximum amount of deposit in a single day 
	[Sys_WT] INT NOT NULL, -- Maximum time of withdrawal in a single day
	[Sys_DT] INT NOT NULL, -- Maximum time of deposit in a single day
	[Sys_MAT] FLOAT NOT NULL, -- Maximum amount of per transfer
	[Sys_AIM] FLOAT NOT NULL, -- Amount in multiple of value
	[Sys_FC1] FLOAT NOT NULL, -- Fast cash value level 1
	[Sys_FC2] FLOAT NOT NULL, -- Fast cash value level 2
	[Sys_FC3] FLOAT NOT NULL, -- Fast cash value level 3
	[Sys_FC4] FLOAT NOT NULL, -- Fast cash value level 4
	[Sys_FC5] FLOAT NOT NULL, -- Fast cash value level 5
	[Sys_FC6] FLOAT NOT NULL, -- Fast cash value level 6
)
--- create table - TB_SYSTEM_REPORT ---
GO
CREATE TABLE [TB_SYSTEM_REPORT]
(
	[Rep_ID] INT IDENTITY PRIMARY KEY,
	[Rep_Title] VARCHAR(100),
	[Rep_Description] VARCHAR(MAX),
	[Rep_Time] DATETIME DEFAULT(GETDATE())
)
--- create procedure - sp_insert_system_report ---
GO
CREATE PROC [sp_insert_system_report]
	@sTitle VARCHAR(1000),
	@sDescription VARCHAR(MAX) = ''
AS
BEGIN
	INSERT INTO [TB_SYSTEM_REPORT]([Rep_Title], [Rep_Description])
		VALUES(@sTitle, @sDescription)
END
--- create procedure - sp_insert_admin ---
GO
CREATE PROC [sp_insert_admin]
	@nActionAdmin INT,
	@sUsername VARCHAR(16),
	@sPassword VARCHAR(40),
	@nPerID INT,
	@sFullName VARCHAR(40) = '',	
	@bGender BIT = 'TRUE',
	@dDoB DATETIME = NULL,
	@sAddress VARCHAR(300) = '',
	@sPhone VARCHAR(15) = '',
	@sEmail VARCHAR(40) = ''	
AS
BEGIN
	INSERT INTO 
		[TB_ADMINISTRATOR]([Admin_FullName], [Admin_Username], [Admin_Password], [Per_ID]
							, [Admin_Gender],[Admin_DoB], [Admin_Address]
							, [Admin_Phone], [Admin_Email])
		VALUES(@sFullName, @sUsername, @sPassword, @nPerID
				, @bGender,@dDoB, @sAddress
				, @sPhone, @sEmail)
	DECLARE @nAdmin INT
	SET @nAdmin = (SELECT [Admin_ID] FROM [TB_ADMINISTRATOR] WHERE [Admin_Username] = @sUsername)
	DECLARE @sDes VARCHAR(100)
	IF @nActionAdmin = @nAdmin
	BEGIN
		SET @sDes = ('Create new Administrator ID ' 
						+ CONVERT(VARCHAR(10),@nActionAdmin)
						+ ' (Username: ' 
						+ @sUsername + ')')
	END
	ELSE
	BEGIN
		SET @sDes = ('Administrator ID ' 
						+ CONVERT(VARCHAR(10),@nActionAdmin)
						+ ': create new Administrator username ' 
						+ @sUsername)
	END
	EXEC sp_insert_system_report 'System', @sDes
END
--- create procedure - sp_validate_login ---
GO
CREATE PROC [sp_validate_login]
	@sUsername VARCHAR(16),
	@sPassword VARCHAR(40)
AS
BEGIN
	DECLARE @nID INT	
	SET @nID = (SELECT [Admin_ID] FROM [TB_ADMINISTRATOR]
					WHERE	[Admin_Username] = @sUsername 
						AND [Admin_Password] = @sPassword)
	DECLARE @sDes VARCHAR(MAX)
	
	IF (@nID IS NOT NULL)
		BEGIN
		SET @sDes = ('Administrator ID ' 
					+ CONVERT(VARCHAR(10),@nID)
					+ ': login system')
		EXEC [sp_insert_system_report] 'System', @sDes
		END
	SELECT * FROM [TB_ADMINISTRATOR], [TB_PERMISSION]
				WHERE	[Admin_Username] = @sUsername 
					AND [Admin_Password] = @sPassword
					AND [TB_ADMINISTRATOR].[Per_ID] = [TB_PERMISSION].[Per_ID]
					AND [Admin_Delete] = 'FALSE'
END

--- create procedure - sp_check_acc ---
GO
CREATE PROC [sp_check_acc]
	@sAccNo VARCHAR(16)
AS
BEGIN
	SELECT * FROM [TB_USER], [TB_ACCOUNT]
		WHERE	[TB_USER].[User_ID] = TB_ACCOUNT.[User_ID]
			AND	[Acc_No] LIKE @sAccNo
			AND [Acc_Delete] = 'FALSE'
			AND [User_Delete] = 'FALSE'
END

--- create procedure - sp_change_pin ---
GO
CREATE PROC [sp_change_pin]	
	@nAccID INT,
	@sAccPIN VARCHAR(40),
	@nActionAdmin INT = NULL
AS
BEGIN
	UPDATE [TB_ACCOUNT]
		SET [Acc_PIN] = @sAccPIN
		WHERE [Acc_ID] = @nAccID	
	DECLARE @sDes VARCHAR(100)
	IF @nActionAdmin IS NULL
		BEGIN
			SET @sDes = ('Account ID ' 
					+ CONVERT(VARCHAR(10),@nAccID)
					+ ': change PIN')
		END
	ELSE
		BEGIN
			SET @sDes = ('Administrator ID ' 
					+ CONVERT(VARCHAR(10),@nActionAdmin)
					+ ': reset PIN Account ID '
					+ CONVERT(VARCHAR(10),@nAccID))
		END
	EXEC sp_insert_system_report 'Account', @sDes
END


--- create procedure - sp_insert_bank_info ---
GO
CREATE PROC sp_insert_bank_info
	@nID INT,
	@sName VARCHAR(40),
	@sAddress VARCHAR(300),
	@sPhone VARCHAR(15),
	@sFax VARCHAR(15)
AS
BEGIN
	INSERT INTO [TB_BANK_INFO]
		VALUES(@nID, @sName, @sAddress, @sPhone, @sFax)
END

--- create procedure - sp_insert_atm_info ---
GO
CREATE PROC sp_insert_atm_info
	@nID INT, @fWA FLOAT,	@fDA FLOAT, @nWT INT, @nDT INT, @fMAT FLOAT,
	@fAIM FLOAT, @fFC1 FLOAT, @fFC2 FLOAT, @fFC3 FLOAT,
	@fFC4 FLOAT, @fFC5 FLOAT, @fFC6 FLOAT
AS
BEGIN
	INSERT INTO [TB_ATM_INFO]
		VALUES(@nID, @fWA, @fDA, @nWT, @nDT, @fMAT, @fAIM
					, @fFC1, @fFC2, @fFC3, @fFC4, @fFC5, @fFC6)
END

--- create procedure - sp_create_new_user ---
GO
CREATE PROC sp_create_new_user
	@nActionAdmin INT,
	@sAccNo VARCHAR(16),
	@sPIN VARCHAR(40),
	@fBalance FLOAT,
	@sFullName VARCHAR(40),
	@sIdCardNo VARCHAR(9),
	@bGender BIT,
	@dDoB DATETIME,	
	@sPhone VARCHAR(15),
	@sAddress VARCHAR(300)
AS
BEGIN
	INSERT INTO TB_USER([User_ID_Card_No], [User_FullName], [User_DoB],
							[User_Gender], [User_Phone], [User_Address])
		VALUES (@sIdCardNo, @sFullName, @dDoB,
					@bGender, @sPhone, @sAddress)
	
	DECLARE @nUserID INT
	SET @nUserID = (SELECT [User_ID] FROM [TB_USER] WHERE [User_ID_Card_No] = @sIdCardNo)

	INSERT INTO TB_ACCOUNT([User_ID], [Acc_No], [Acc_PIN], [Acc_Balance])
		VALUES(@nUserID, @sAccNo, @sPIN, @fBalance)
	DECLARE @nAccID INT
	SET @nAccID = (SELECT [Acc_ID] FROM [TB_ACCOUNT] WHERE [User_ID] = @nUserID)
	DECLARE @sDes VARCHAR(100)
	SET @sDes = ('Administrator ID ' 
					+ CONVERT(VARCHAR(10),@nActionAdmin)
					+ ': create new User ID ' 
					+ CONVERT(VARCHAR(10),@nUserID)
					+ ' (Account ID ' 
					+ CONVERT(VARCHAR(10),@nAccID) + ')')
	EXEC sp_insert_system_report 'System', @sDes
END

GO
INSERT INTO TB_PERMISSION VALUES (1, 'Administrator')
GO
INSERT INTO TB_PERMISSION VALUES (2, 'Manager')
GO
sp_insert_atm_info 1, 25000, 25000, 5, 5, 100000, 20, 20, 50, 100, 150, 300, 500
GO
sp_insert_bank_info 1, 'QV2T Bank', '55 Giai Phong, Ha Noi, Viet Nam','01638712299','01689962023'
GO
sp_insert_admin 1, 'admin', 'admin', 1
GO
sp_create_new_user 1, '0000000000000000', '0000', 10000, 'Dang Sy Quynh', '123562789', 'TRUE', '05-17-1990', '01638712299', 'Ha Noi, Viet Nam'